package com.neuron.trafikanten.db;

import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;

import com.neuron.trafikanten.HelperFunctions;
import com.neuron.trafikanten.dataSets.StationData;
import com.neuron.trafikanten.search.BuildAutocompleteCursor.MyMatrixCursor;
import com.neuron.trafikanten.search.StationSearchProvider;

/**
 * Generic station db adapter, this is used for storing Favorites/History.
 * @author neuron
 *
 */
public class StationDbAdapter {
    public static final String KEY_ROWID = "_id";
    public static final String KEY_TYPE = "type";
    public static final String KEY_LASTUSED = "lastUsed";
    public static final String KEY_STATIONID = "stationid";
	public static final String KEY_STOPNAME = "stopname";
	public static final String KEY_USED = "used";
	public static final String KEY_EXTRA = "extra";
	public static final String KEY_REALTIMESTOP = "realtimestop";
	public static final String KEY_UTM_X = "utm_x";
	public static final String KEY_UTM_Y = "utm_y";
	
	public static final int TYPE_HISTORY = 1;
	
	private static final String TABLE = "stations";
	private static final int DATABASE_VERSION = 1;
	private DatabaseHelper dbHelper;
	
    private class DatabaseHelper extends SQLiteOpenHelper {
    	private Context mContext;
    	public boolean newDatabase = false;
    	
        DatabaseHelper(Context context) {
        	super(context, "stations.db", null, DATABASE_VERSION);
        	this.mContext = context;
        }

        private void createTables(SQLiteDatabase db) {
            db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE + " (" +
            		"_id integer primary key autoincrement," +
            		"type int not null," +
            		"used int not null," +
            		"lastUsed int not null," + 
            		"stationid int unique," +
            		"stopname varchar(256) not null," +
            		"extra text," +
            		"realtimestop boolean not null," +
            		"utm_x int," + 
            		"utm_y int);");
        }
        
        @Override
        public void onCreate(SQLiteDatabase db) {
        	newDatabase = true;
        	createTables(db);
        }
        
        /**
         * Migrate from version 2.2.2, takes argument since we want both history and favorites
         */
        public void migrate222(String database) {
    		try {
    			SQLiteDatabase db = SQLiteDatabase.openDatabase("/data/data/com.neuron.trafikanten/databases/" + database, null, SQLiteDatabase.OPEN_READONLY);
    			try {
    				Cursor cursor = db.rawQuery("SELECT stopname, extra, stationid, realtimestop, utmX, utmY, used FROM Trafikanten" , null);
    				try {
    					while (cursor.moveToNext()) {
    	    	    		final StationData station = new StationData(cursor.getString(0), 
    	    	    				cursor.getString(1), 
    	    	    				cursor.getInt(2),
    	    	    				cursor.getInt(3) == 1, 
    	    	    				new int[] {cursor.getInt(4), cursor.getInt(5)});
    	    	    		if (getType(station) == -1) {
    	    	    			add(station, TYPE_HISTORY, cursor.getInt(6));
    	    	    		}
    					}
    				} finally {
    					cursor.close();
    				}
    			} finally {
        			db.close();
        		}
    		} catch(SQLiteException e) {}
        }

        
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        	Toast.makeText(mContext, "Upgrading database, deleting all old station data", Toast.LENGTH_SHORT).show();
            db.execSQL("DROP TABLE IF EXISTS " + TABLE);
            createTables(db);
        }
    }
    
    public void close() {
    	dbHelper.close();
    }
    
    //private static StationDbAdapter sInstance;
    public static StationDbAdapter getInstance(Context context) {
    	/*if (sInstance == null) {
    		sInstance = new StationDbAdapter(context);
    	}
    	return sInstance;*/
    	// Can't do instancing with dbHelper... it needs to be properly closed.
    	return new StationDbAdapter(context);
    }
    
    private StationDbAdapter(Context context) { 
    	dbHelper = new DatabaseHelper(context);
    	
    	// Create initial table:
    	SQLiteDatabase db = dbHelper.getWritableDatabase();

		// Delete entries that haven't been used in 4 weeks.
		long oldTime = System.currentTimeMillis() - (HelperFunctions.DAY * (7 * 4));
		db.rawQuery("DELETE FROM stations WHERE lastUsed < " +  oldTime, null);
		
		if (dbHelper.newDatabase) {
    		// TRY TO MIGRATE DATABASE FROM LEGACY < 2.2.3 systems
			dbHelper.migrate222("history");
			dbHelper.migrate222("favorites");

		}

    }
    
    /*
     * Add station to list
     */
    public long add(StationData station, int type, int used) {
    	SQLiteDatabase db = dbHelper.getWritableDatabase();

		station.getLongLat(); // cache this in database when possible
    	final ContentValues values = new ContentValues();
    	values.put(KEY_STATIONID, station.stationId);
    	values.put(KEY_TYPE, type);
    	values.put(KEY_STOPNAME, station.stopName);
    	values.put(KEY_USED, used);
    	values.put(KEY_LASTUSED, System.currentTimeMillis());
    	values.put(KEY_EXTRA, station.extra);
    	values.put(KEY_REALTIMESTOP, station.realtimeStop);
    	values.put(KEY_UTM_X, station.utmCoords[0]);
    	values.put(KEY_UTM_Y, station.utmCoords[1]);
    	
    	return db.insert(TABLE, null, values);
    }
    
    public long add(StationData station, int type) {
    	return add(station, type, TYPE_HISTORY);
    }
    
    /*
     * Delete station from list.
     */
    public boolean delete(StationData station) {
    	SQLiteDatabase db = dbHelper.getWritableDatabase();
    	return db.delete(TABLE, KEY_STATIONID + "=" + station.stationId, null) > 0;
    }
    
    public int getType(StationData station) {
    	SQLiteDatabase db = dbHelper.getReadableDatabase();
		final Cursor cursor = db.rawQuery("SELECT type FROM stations WHERE stationid=" + station.stationId, null);
		try {
			if (cursor.moveToFirst()) {
				// Data already exists in database. Run update
				return cursor.getInt(0);
			}
		} finally {
			cursor.close();
		}
		return -1;
    }
    
    /*
     * Station was just used, add to history if neccesary.
     */
    public void stationUsed(StationData station) {
    	SQLiteDatabase db = dbHelper.getWritableDatabase();
		if (getType(station) != -1) {
			Log.d("DEBUG CODE","stationUsed update station " + station.stopName);
			
			// We can only update realtimestop if we have an actual realtimestop. As the route autocomplete api doesn't include this information, and we dont want to accidentally remove it.
			String realtimeStopSql = ""; 
			if (station.realtimeStop) {
				realtimeStopSql = ", " + KEY_REALTIMESTOP + " = 1";
			}
			final String sql = String.format("UPDATE %s SET %s = %s + 1, %s = ? %s, %s = %d WHERE %s = %d", TABLE, KEY_USED, KEY_USED, KEY_EXTRA, realtimeStopSql, KEY_LASTUSED, System.currentTimeMillis(), KEY_STATIONID, station.stationId);
			final Cursor cursor = db.rawQuery(sql, new String[] { station.extra });
			try {
				cursor.moveToFirst();
				
			} finally {
				cursor.close();
			}
		} else {
			Log.d("DEBUG CODE","stationUsed add station " + station.stopName + " " + station.extra);
			add(station, TYPE_HISTORY);
		}
  
    }
    
    /**
     * Adds history items to list
     * @param items = list of station data
     * @param filterRealtime = filter on realtime stations only
     */
    public void addToList(List<StationData> items, boolean filterRealtime) {
    	final String[] COLUMNS = new String[] { KEY_STOPNAME, 
    		KEY_EXTRA,
    		KEY_STATIONID,
    		KEY_REALTIMESTOP,
    		KEY_UTM_X,
    		KEY_UTM_Y};
    	SQLiteDatabase db = dbHelper.getReadableDatabase();
		final Cursor cursor = db.query(TABLE, COLUMNS, null, null, null, null, KEY_USED + " DESC", "20");
		try {
			while (cursor.moveToNext()) {
	    		final StationData station = new StationData(cursor.getString(0), 
	    				cursor.getString(1), 
	    				cursor.getInt(2),
	    				cursor.getInt(3) == 1, 
	    				new int[] {cursor.getInt(4), cursor.getInt(5)});
	    		if (filterRealtime && !station.realtimeStop) {
	    			continue;
	    		}
	    		items.add(station);
			}
		} finally {
			cursor.close();
		}
 
    }

    /*
     * Do a autocomplete search
     */
	public void autoComplete(MyMatrixCursor ret, String selection,
			String query) {
    	final String[] COLUMNS = new String[] { KEY_STATIONID, KEY_STOPNAME, KEY_EXTRA, KEY_REALTIMESTOP};
    	SQLiteDatabase db = dbHelper.getReadableDatabase();

		boolean filterRealtime = selection.equals(StationSearchProvider.SEARCHTYPE_REALTIME);

		String searchSelector = null;
		String[] searchQuery = null;
		if (query != null && query.length() > 0) {
			searchSelector = KEY_STOPNAME + " LIKE ?";
			searchQuery = new String[] { query + '%' };
		}

		final Cursor cursor = db.query(TABLE, COLUMNS, searchSelector, searchQuery, null, null, KEY_USED + " DESC");
		try {
			while (cursor.moveToNext()) {
    			boolean realtime = cursor.getInt(3) == 1;
    			if (filterRealtime && !realtime) {
    				continue;
    			}
    			ret.addRow(cursor.getInt(0), cursor.getString(1), cursor.getString(2), 0);
			}
		} finally {
			cursor.close();
		}

	}
}
